#!/usr/bin/env python
# -*- coding: utf-8 -*-
import pymysql
import datetime
import time
import os



def connMySQL(sql,args):
        try:
            conn=pymysql.connect(host='121.201.72.168',user='daxmall_r',passwd='2hGUnCKoEuvkWNgRbvwo',db='daxmall',port=56033,charset="utf8")
            #conn=pymysql.connect(host='10.0.0.111',user='uv_stat',passwd='3rMdKe4I9T37fxgNuKVF',db='daxmall',port=3306,charset="utf8")
            cur=conn.cursor()
            cur.execute(sql % args)
            #cur.execute('flush privileges')
            results = cur.fetchall()
            return results

            cur.close()
            conn.close()
        except pymysql.Error as e:
             print ("Mysql Error %d: %s" % (e.args[0], e.args[1]))

def connMySQL_insert(sql,args):
        try:
            conn=pymysql.connect(host='121.201.68.21',user='jiang',passwd='jiangwenhui',db='trans_data',port=3307,charset='utf8')
            cur=conn.cursor()
            cur.execute(sql % args)
            cur.execute('commit')
            #cur.execute('flush privileges')
            #results = cur.fetchall()
            cur.close()
            conn.close()
        except pymysql.Error as e:
             print ("Mysql Error %d: %s" % (e.args[0], e.args[1]))


if __name__ == '__main__':
	now = int(time.time())
	timeArray = time.localtime(now)
	nowStyleTime = time.strftime("%Y-%m-%d %H:%M:%S", timeArray)
	# print nowStyleTime

	fiveMinAgo = now - 300
	timeArray = time.localtime(fiveMinAgo)
	fiveMinAgoStyleTime = time.strftime("%Y-%m-%d %H:%M:%S", timeArray)
	# print fiveMinAgoStyleTime

	args = (fiveMinAgoStyleTime,nowStyleTime)

	#sql_1 = 'SELECT count(1) AS 订单数,SUM(product_nums) AS 商品数,SUM(total) AS 总金额 FROM ls_sub WHERE `update_date` >= "%s" AND `update_date` < "%s"'
	sql_1 = 'SELECT count(1) AS 订单数,SUM(product_nums) AS 商品数, SUM(actual_total) AS 总金额 FROM ls_sub WHERE `update_date` >= "%s" AND `update_date` < "%s"'
	info = connMySQL(sql_1,args)
	# print info
	if info[0][0] is None:
		a1 = 0
	else:a1 = info[0][0]
	if info[0][1] is None:
		a2 = 0
	else:a2 = info[0][1]
	if info[0][2] is None:
		a3 = 0
	else:a3 = info[0][2]
	# print a1,a2,a3

	sql_1_insert = 'insert into sub_realtime (sub_count,good_count,total,time_) values (%d,%d,%f,"%s")'
	args_1_insert = (a1,a2,a3,nowStyleTime)
	connMySQL_insert(sql_1_insert,args_1_insert)
	'*********************************************'

	# print "\n"
	"""
	"""
	sql_3 = 'SELECT \
		c.parent_id AS 商品二级类目ID, \
		SUM(i.basket_count) AS 订单商品总数, \
		round(SUM(i.price * i.basket_count),0) AS 订单总金额 \
	FROM \
		ls_sub s \
	INNER JOIN ls_sub_item i ON i.sub_number = s.sub_number \
	INNER JOIN ls_prod p ON p.prod_id = i.prod_id \
	INNER JOIN ls_category c ON c.id = p.category_id \
	WHERE \
		s.update_date >= "%s" \
	AND s.update_date < "%s" \
	AND s. STATUS != 5 \
	GROUP BY \
		c.parent_id'

	sql_4 = 'SELECT \
		p.category_id AS 商品三级类目ID, \
		SUM(i.basket_count) AS 订单商品总数, \
		round(SUM(i.price * i.basket_count),0) AS 订单总金额 \
	FROM \
		ls_sub s \
	INNER JOIN ls_sub_item i ON i.sub_number = s.sub_number \
	INNER JOIN ls_prod p ON p.prod_id = i.prod_id \
	INNER JOIN ls_category c ON c.id=p.category_id \
	WHERE \
		c.grade = 3 \
	AND s.update_date >= "%s" \
	AND s.update_date < "%s" \
	AND s. STATUS != 5 \
	GROUP BY \
		p.category_id'
	sql_3_insert = 'insert into sub_category (cate_id,good_count,total,time_) values (%d,%d,%f,"%s")'
	sql_4_insert = 'insert into sub_category_det (cate_id,good_count,total,time_) values (%d,%d,%f,"%s")'
	info = connMySQL(sql_3,args)
	info2 = connMySQL(sql_4,args)
	for i in info:
		# print i[0],i[1],i[2]
		args_3 = (i[0],i[1],i[2],nowStyleTime)
		connMySQL_insert(sql_3_insert,args_3)
	'*********************************************'
	# print "\n"
	for i in info2:
		# print i[0],i[1],i[2]
		args_4 = (i[0],i[1],i[2],nowStyleTime)
		connMySQL_insert(sql_4_insert,args_4)
	'*********************************************'
	# print "\n"
	"""	
	"""
	sql_7 = 'SELECT r.city_id AS 城市ID,COUNT(1) AS 订单数量,SUM(s.product_nums) AS 商品数量,SUM(s.total) AS 订单总金额 \
	FROM ls_sub s \
	    INNER JOIN ls_usr_addr_sub a ON a.`addr_order_id`=s.`addr_order_sn` \
	    INNER JOIN ls_usr_addr r ON r.`addr_id`=a.`addr_id` \
	WHERE s.update_date>="%s" AND s.update_date < "%s" AND s.status != 5 \
	GROUP BY r.city_id'
	sql_7_insert = 'insert into sub_city (city_id,sub_count,good_count,total,time_) values (%d,%d,%d,%f,"%s")'
	info = connMySQL(sql_7,args)
	for i in info:
		# print i[0],i[1],i[2],i[3]
		args_7 = (i[0],i[1],i[2],i[3],nowStyleTime)
		connMySQL_insert(sql_7_insert,args_7)
	'*********************************************'
	# print "\n"
	sql_8 = 'SELECT r.province_id AS 省份ID,COUNT(1) AS 订单数量,SUM(s.product_nums) AS 商品数量,SUM(s.total) AS 订单总金额 \
	FROM ls_sub s \
	    INNER JOIN ls_usr_addr_sub a ON a.`addr_order_id`=s.`addr_order_sn` \
	    INNER JOIN ls_usr_addr r ON r.`addr_id`=a.`addr_id` \
	WHERE s.update_date>="%s" AND s.update_date < "%s" AND s.status != 5 \
	GROUP BY r.province_id'
	sql_8_insert = 'insert into sub_province (province_id,sub_count,good_count,total,time_) values (%d,%d,%d,%f,"%s")'
	info = connMySQL(sql_8,args)
	for i in info:
		# print i[0],i[1],i[2],i[3]
		args_8 = (i[0],i[1],i[2],i[3],nowStyleTime)
		connMySQL_insert(sql_8_insert,args_8)
	'*********************************************'
	print ("ok")
